import logging
from datetime import datetime
from typing import List, Optional

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_area_router = APIRouter(prefix="/data/area", tags=["客户管理"])


class AreaSearch(PaginationBase):
    fsiteLevel: str = Field(None, description="地区编号(0:省,1:市;2:区,3:学校)")
    fsiteName: str = Field(None, description="地区名称")
    fsiteParent: str = Field(None, description="上级地区")
    fsiteNo: str = Field(None, description="地区编号")


# todo 1.学校资料
@data_area_router.get("/list", summary="客户查询接口")
def list_area(search: AreaSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_site a "
    sql = sql + " left join a_ts_site b on b.fsite_no=a.fsite_parent"
    sql = sql + " where 1=1"
    if search.fsiteLevel is not None:
        sql = sql + " and a.fsite_level = :site_Level "
        param["site_Level"] = search.fsiteLevel
    else:
        sql = sql + " and a.fsite_level < 3"
    if search.fsiteName is not None:
        sql = sql + " and a.fsite_name like concat('%', :site_Name ,'%')"
        param["site_Name"] = search.fsiteName
    if search.fsiteParent is not None:
        sql = sql + " and a.fsite_parent = :site_Parent "
        param["site_Parent"] = search.fsiteParent
    if search.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fsite_no,a.fsite_name,a.fsite_parent,a.fsite_level,a.fsite_l,a.fsite_d,a.fengineer,"
    sql = sql + " a.fphone,a.ftelno,a.femail,a.fimage,a.fsite_state,a.fupddate,a.foperator,a.fso2,"
    sql = sql + " a.fno2,a.fco,a.fo3,a.fpm25,a.fpm10,a.fwd,a.fsd,a.noise,a.faqi,a.faqi_tb,a.faqi_hb,a.fcontaminants,"
    sql = sql + " a.fpm26,a.fpm11,a.fco2,a.fjq,a.fyjw,a.fwd2,a.fsd2,a.faqi2,a.fcontaminants2,a.fnetwork_ok,"
    sql = sql + " a.fnetwork_no,a.frefresh,a.fsite_type,a.day_off,a.fbegtime,a.fendtime,a.fimage3d,"
    sql = sql + " a.fboxflag,a.fuseflag,a.flink_man,a.fusetype,a.finvtype,a.fouttype,a.fapiflag,a.fcourseflag,"
    sql = sql + " a.fbegindate,a.fenddate,a.fcharge,a.fchargeprice,a.fappurl,a.fappid,a.fappkey,a.fappsecret"
    sql = sql + " ,b.fsite_name parentFsiteName"
    sql = sql + " from a_ts_site a "
    sql = sql + " left join a_ts_site b on b.fsite_no=a.fsite_parent"
    sql = sql + " where 1=1"
    if search.fsiteLevel is not None:
        sql = sql + " and a.fsite_level = :site_Level "
        param["site_Level"] = search.fsiteLevel
    else:
        sql = sql + " and a.fsite_level<3"
    if search.fsiteName is not None:
        sql = sql + " and a.fsite_name like concat('%', :site_Name ,'%')"
        param["site_Name"] = search.fsiteName
    if search.fsiteParent is not None:
        sql = sql + " and a.fsite_parent = :site_Parent "
        param["site_Parent"] = search.fsiteParent
    if search.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    sql = sql + " limit :limit offset :offset"
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()
    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteNo'] = data[key][0]
        d1['fsiteName'] = data[key][1]
        d1['fsiteParent'] = data[key][2]
        d1['fsiteLevel'] = data[key][3]
        d1['fsiteL'] = data[key][4]
        d1['fsiteD'] = data[key][5]
        d1['fengineer'] = data[key][6]
        d1['fphone'] = data[key][7]
        d1['ftelno'] = data[key][8]
        d1['femail'] = data[key][9]
        d1['fimage'] = data[key][10]
        d1['fsiteState'] = data[key][11]
        d1['fupddate'] = data[key][12]
        d1['foperator'] = data[key][13]
        d1['fso2'] = data[key][14]
        d1['fno2'] = data[key][15]
        d1['fco'] = data[key][16]
        d1['fo3'] = data[key][17]
        d1['fpm25'] = data[key][18]
        d1['fpm10'] = data[key][19]
        d1['fwd'] = data[key][20]
        d1['fsd'] = data[key][21]
        d1['noise'] = data[key][22]
        d1['faqi'] = data[key][23]
        d1['faqiTb'] = data[key][24]
        d1['faqiHb'] = data[key][25]
        d1['fcontaminants'] = data[key][26]
        d1['fpm26'] = data[key][27]
        d1['fpm11'] = data[key][28]
        d1['fco2'] = data[key][29]
        d1['fjq'] = data[key][30]
        d1['fyjw'] = data[key][31]
        d1['fwd2'] = data[key][32]
        d1['fsd2'] = data[key][33]
        d1['faqi2'] = data[key][34]
        d1['fcontaminants2'] = data[key][35]
        d1['fnetworkOk'] = data[key][36]
        d1['fnetworkNo'] = data[key][37]
        d1['frefresh'] = data[key][38]
        d1['fsiteType'] = data[key][39]

        dateLevel = data[key][40]
        weekList = list()
        try:
            if dateLevel[0] == "1":
                weekList.append("1")
            if dateLevel[1] == "1":
                weekList.append("2")
            if dateLevel[2] == "1":
                weekList.append("3")
            if dateLevel[3] == "1":
                weekList.append("4")
            if dateLevel[4] == "1":
                weekList.append("5")
            if dateLevel[5] == "1":
                weekList.append("6")
            if dateLevel[6] == "1":
                weekList.append("7")
        except Exception as e:
            print(e)
        d1['dayOff'] = weekList
        d1['fbegtime'] = data[key][41]
        d1['fendtime'] = data[key][42]
        d1['fimage3d'] = data[key][43]
        d1['fboxflag'] = data[key][44]
        d1['fuseflag'] = data[key][45]
        d1['flinkMan'] = data[key][46]
        d1['fusetype'] = data[key][47]
        d1['finvtype'] = data[key][48]
        d1['fouttype'] = data[key][49]
        d1['fapiflag'] = data[key][50]
        d1['fcourseflag'] = data[key][51]
        d1['fbeginDate'] = data[key][52]
        d1['fendDate'] = data[key][53]
        d1['fcharge'] = data[key][54]
        d1['fchargeprice'] = data[key][55]
        d1['fappurl'] = data[key][56]
        d1['fappid'] = data[key][57]
        d1['fappkey'] = data[key][58]
        d1['fappsecret'] = data[key][59]
        d1['parentFsiteName'] = data[key][60]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": search.limit, "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class SiteCreate(BaseModel):
    """地区资料创建模型"""
    fsiteNo: str = Field(..., description="地区编号", min_length=1)
    fsiteName: str = Field(..., description="地区名称", min_length=1)
    fsiteParent: str = Field("0", description="父级地区编号")  # 默认值为"0"
    fsiteLevel: int = Field(..., description="地区级别")
    fsiteL: float = Field(0, description="经度")
    fsiteD: float = Field(0, description="纬度")
    fsiteState: int = Field(..., description="状态（1:启用，0:停用）")
    fupddate: datetime = Field(default=datetime.now(), description="更新日期")  # 格式建议:YYYY-MM-DD
    foperator: str = Field(..., description="操作人")

    # 可选联系信息
    fphone: str = Field("", description="联系电话")
    flinkMan: str = Field("", description="联系人")
    ftelno: str = Field("", description="固定电话")
    femail: str = Field("", description="邮箱")
    fimage3d: str = Field("", description="3D图片路径")

    # 业务字段（默认值与代码逻辑一致）
    fsiteType: int = Field(2, description="收费方式（1:按次，2:按小时）")  # 默认按小时
    fcharge: int = Field(1, description="是否收费（1:是，0:否）")  # 默认收费
    fchargeprice: float = Field(0.0, description="收费单价")  # 默认0元


@data_area_router.post("/create", summary="新增客户资料", dependencies=[Depends(oauth2_scheme)])
def create_data_area(site: SiteCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select fsite_no from a_ts_site where fsite_no= :fsiteNo "
            data = db.execute(text(sql), {
                "fsiteNo": site.fsiteNo,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="编号已存在")
            else:
                sql = "insert into a_ts_site(fsite_no,fsite_name,fsite_parent,fsite_level,fsite_l,fsite_d,fsite_state,fupddate,foperator,fphone,flink_man,ftelno,femail,fimage3d,fsite_type,fcharge,fchargeprice)"
                sql = sql + (
                    " values( :fsiteNo , :fsiteName , :fsiteParent , :fsiteLevel , :fsiteL , :fsiteD ,:fsiteState , :fupddate , :foperator ,"
                    " :fphone , :flinkMan , :ftelno , :femail , :fimage3d , :fsiteType ,1,0);")
                db.execute(text(sql), {
                    "fsiteNo": site.fsiteNo,
                    "fsiteName": site.fsiteName,
                    "fsiteParent": site.fsiteParent,
                    "fsiteLevel": site.fsiteLevel,
                    "fsiteL": site.fsiteL,
                    "fsiteD": site.fsiteD,
                    "fsiteState": site.fsiteState,
                    "fupddate": site.fupddate,
                    "foperator": site.foperator,
                    "fphone": site.fphone,
                    "flinkMan": site.flinkMan,
                    "ftelno": site.ftelno,
                    "femail": site.femail,
                    "fimage3d": site.fimage3d,
                    "fsiteType": site.fsiteType,
                })

                # 返回
                d1 = {}
                d1['fsiteNo'] = site.fsiteNo
                d1['fsiteName'] = site.fsiteName
                d1['fsiteParent'] = site.fsiteParent
                d1['fsiteLevel'] = site.fsiteLevel
                d1['fsiteL'] = site.fsiteL
                d1['fsiteD'] = site.fsiteD
                d1['fengineer'] = None
                d1['fphone'] = None
                d1['ftelno'] = None
                d1['femail'] = None
                d1['fimage'] = None
                d1['fsiteState'] = site.fsiteState
                d1['fupddate'] = site.fupddate
                d1['foperator'] = site.foperator
                d1['fso2'] = None
                d1['fno2'] = None
                d1['fco'] = None
                d1['fo3'] = None
                d1['fpm25'] = None
                d1['fpm10'] = None
                d1['fwd'] = None
                d1['fsd'] = None
                d1['noise'] = None
                d1['faqi'] = None
                d1['faqitb'] = None
                d1['faqihb'] = None
                d1['fcontaminants'] = None
                d1['fpm26'] = None
                d1['fpm11'] = None
                d1['fco2'] = None
                d1['fjq'] = None
                d1['fyjw'] = None
                d1['fwd2'] = None
                d1['fsd2'] = None
                d1['faqi2'] = None
                d1['fcontaminants2'] = None
                d1['fnetworkok'] = None
                d1['fnetworkno'] = None
                d1['frefresh'] = None
                d1['fsiteType'] = None
                d1['dayoff'] = None
                d1['fbegtime'] = None
                d1['fendtime'] = None
                d1['fimage3d'] = None
                d1['fboxflag'] = None
                d1['fuseflag'] = None
                d1['flinkMan'] = None
                d1['fusetype'] = None
                d1['finvtype'] = None
                d1['fouttype'] = None
                d1['fapiflag'] = None
                d1['fcourseflag'] = None
                d1['fbegindate'] = None
                d1['fenddate'] = None
                d1['fcharge'] = None
                d1['fchargeprice'] = None
                d1['fappurl'] = None
                d1['fappid'] = None
                d1['fappkey'] = None
                d1['fappsecret'] = None
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class ConsumableCategoryUpdate(BaseModel):
    """耗材分类修改模型"""
    fsiteNo: str = Field(..., description="站点编号")
    fsiteState: int = Field(..., description="站点状态")
    fupddate: str = Field(..., description="更新日期")

    # 可选字段（有默认值）
    fsiteName: str = Field("", description="站点名称")
    fsiteParent: str = Field("", description="父站点")
    fsiteLevel: str = Field("", description="站点级别")
    fsiteL: str = Field("", description="站点L")
    fsiteD: str = Field("", description="站点D")
    fsiteType: str = Field("1", description="站点类型")
    fphone: str = Field("", description="联系电话")
    ftelno: str = Field("", description="电话号码")
    flinkMan: str = Field("", description="联系人")
    femail: str = Field("", description="邮箱")
    fengineer: str = Field("0", description="工程师")
    fbegtime: str = Field("", description="开始时间")
    fendtime: str = Field("", description="结束时间")

    # 执行周列表（用于计算day_off）
    fexecweekList: List[str] = Field([], description="执行周列表")

    # 自动计算字段
    day_off: str = Field("", description="休息日（自动计算，7位二进制字符串）")

    # 操作人（从token获取）
    foperator: Optional[int] = Field(None, description="操作人ID")


@data_area_router.post("/update", summary="耗材分类修改接口")
def update_data_area(consum: ConsumableCategoryUpdate, db: Session = Depends(get_db),
                     token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            fexecweekList = consum.fexecweekList
            dataLevel = ""
            i = 0
            while i <= 6:
                if str(i + 1) in fexecweekList:
                    dataLevel = dataLevel + "1"
                else:
                    dataLevel = dataLevel + "0"
                i = i + 1
            sql = "update a_ts_site"
            sql = sql + " set fsite_name= :fsiteName ,fsite_parent= :fsiteParent "
            sql = sql + ",fsite_level= :fsiteLevel ,fsite_l= :fsiteL ,fsite_d= :fsiteD "
            sql = sql + ",day_off= :dataLevel ,fsite_type= :fsiteType ,ftelno= :ftelno "
            sql = sql + ",flink_man= :flinkMan ,femail= :femail ,fengineer= :fengineer "
            sql = sql + ",fbegtime= :fbegtime ,fendtime= :fendtime ,fphone= :fphone "
            sql = sql + ",fsite_state= :fsiteState ,fupddate= :fupddate ,foperator= :userid "
            sql = sql + " where fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fsiteName": consum.fsiteName,
                "fsiteParent": consum.fsiteParent,
                "fsiteLevel": consum.fsiteLevel,
                "fsiteL": consum.fsiteL,
                "fsiteD": consum.fsiteD,
                "dataLevel": dataLevel,
                "fsiteType": consum.fsiteType,
                "ftelno": consum.ftelno,
                "flinkMan": consum.flinkMan,
                "femail": consum.femail,
                "fengineer": consum.fengineer,
                "fbegtime": consum.fbegtime,
                "fendtime": consum.fendtime,
                "fphone": consum.fphone,
                "fsiteState": consum.fsiteState,
                "fupddate": consum.fupddate,
                "userid": user_id,
                "fsiteNo": consum.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_area_router.post("/delete/{fsiteNo}")
def delete_data_area(fsiteNo: str = Path(description="监测点编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select sum(f1) "
            sql = sql + " from ( "
            sql = sql + " select count(1) f1 from a_ts_point where fsite_no= :fsiteNo "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from a_ts_layer where fsite_no= :fsiteNo "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from a_ts_site where fsite_parent= :fsiteNo ) a"
            sql = sql + " where 1=1"
            data = db.execute(text(sql), {"fsiteNo": fsiteNo}).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from a_ts_site where fsite_no= :fsiteNo "
                db.execute(text(sql), {"fsiteNo": fsiteNo})
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_area_router.get("/fsiteNoBelongOptions/{level}", summary="地区资料接口")
def list_get_fsite(level: int = Path(description="等级"), db: Session = Depends(get_db)):
    sql = "select fsite_no,fsite_name from a_ts_site "
    sql = sql + " where fsite_level!= :level order by fsite_no"
    roledata = db.execute(text(sql), {"level": level}).fetchall()
    data_list = []
    for key in range(len(roledata)):
        d2 = {}
        d2['value'] = roledata[key][0]
        d2['label'] = roledata[key][1]
        data_list.append(d2)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@data_area_router.get("/fsiteOptionsByLevel/{level}", summary="客户资料接口")
def list_site(level: int = Path(description="资料等级"), db: Session = Depends(get_db)):
    sql = "select fsite_no,fsite_name from a_ts_site where fsite_level= :level order by fsite_no"
    data = db.execute(text(sql), {"level": level}).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['value'] = data[key][0]
        d1['label'] = data[key][1]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@data_area_router.get("/fsiteOptionsByLevel2/{level}", summary="根据角色查询客户资料")
def list_site2(level: int = Path(description="资料等级"), db: Session = Depends(get_db)):
    sql = "select fsite_no,fsite_name from a_ts_site where fsite_level= :level order by fsite_no"
    data = db.execute(text(sql), {"level": level}).fetchall()
    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['value'] = data[key][0]
        d1['label'] = data[key][1]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@data_area_router.get("/fpointOptions2/{fsiteNo}", summary="接口")
def list_point_list2(fsiteNo: int = Path(description="客户编号"), db: Session = Depends(get_db)):
    sql = "select fpoint_no,fpoint_name from a_ts_point"
    sql = sql + " where fsite_no= :fsiteNo "
    sql = sql + " and fpoint_flag in (1,2) "
    sql = sql + " order by fpoint_no"
    data = db.execute(text(sql), {"fsiteNo": fsiteNo}).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['value'] = data[key][0]
        d1['label'] = data[key][1]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@data_area_router.get("/fpointOptions4/{fsiteNo}", summary="客户资料接口")
def list_point_list4(fsiteNo: int = Path(description="客户编号"), db: Session = Depends(get_db)):
    sql = "select fpoint_no,fpoint_name from a_ts_point"
    sql = sql + " where fsite_no= :fsiteNo "
    sql = sql + " and fpoint_flag=0"
    sql = sql + " order by fpoint_no"
    data = db.execute(text(sql), {"fsiteNo": fsiteNo}).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['value'] = data[key][0]
        d1['label'] = data[key][1]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class BizSearch(BaseModel):
    pBillNo: str
    pBillState: str
    pBizType: str
    pUser: str


@data_area_router.get("/checkBiz", summary="单据审核接口")
def check_biz(biz: BizSearch = Query(), db: Session = Depends(get_db)):
    sql = "select * from lims.proc_biz_bizcenter('lims." + biz.pBizType + "','', :pBillNo , :pBillState , :pUser );"
    db.execute(text(sql), {
        "pBillNo": biz.pBillNo,
        "pBillState": biz.pBillState,
        "pUser": biz.pUser,
        "pBizType": biz.pBizType
    }).fetchall()
    return BaseResponse.success()


@data_area_router.get("/listAndSave/{fsiteNo}", summary="查询并保存站点信息")
def list_and_save_area(fsiteNo: str = Path(description="客户编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
